{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "58f1cebe-9313-47be-91a7-a292c721fa70",
   "metadata": {},
   "source": [
    "# Installing the libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "043947ca-ebbd-4f3b-973b-02aa8064d407",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: pandas in c:\\users\\ian\\pivot_tables\\venv\\lib\\site-packages (2.2.1)\n",
      "Requirement already satisfied: pyarrow in c:\\users\\ian\\pivot_tables\\venv\\lib\\site-packages (15.0.0)\n",
      "Requirement already satisfied: numpy<2,>=1.26.0 in c:\\users\\ian\\pivot_tables\\venv\\lib\\site-packages (from pandas) (1.26.4)\n",
      "Requirement already satisfied: python-dateutil>=2.8.2 in c:\\users\\ian\\pivot_tables\\venv\\lib\\site-packages (from pandas) (2.9.0.post0)\n",
      "Requirement already satisfied: pytz>=2020.1 in c:\\users\\ian\\pivot_tables\\venv\\lib\\site-packages (from pandas) (2024.1)\n",
      "Requirement already satisfied: tzdata>=2022.7 in c:\\users\\ian\\pivot_tables\\venv\\lib\\site-packages (from pandas) (2024.1)\n",
      "Requirement already satisfied: six>=1.5 in c:\\users\\ian\\pivot_tables\\venv\\lib\\site-packages (from python-dateutil>=2.8.2->pandas) (1.16.0)\n"
     ]
    }
   ],
   "source": [
    "!pip install pandas pyarrow"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "809281a1-0122-4332-b39e-7603e11d1f62",
   "metadata": {},
   "source": [
    "# Reading the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "9d7828ae-7a2a-4beb-b92e-a47786e7eb2c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_number</th>\n",
       "      <th>employee_id</th>\n",
       "      <th>employee_name</th>\n",
       "      <th>job_title</th>\n",
       "      <th>sales_region</th>\n",
       "      <th>order_date</th>\n",
       "      <th>order_type</th>\n",
       "      <th>customer_type</th>\n",
       "      <th>customer_name</th>\n",
       "      <th>customer_state</th>\n",
       "      <th>product_category</th>\n",
       "      <th>product_number</th>\n",
       "      <th>produce_name</th>\n",
       "      <th>quantity</th>\n",
       "      <th>unit_price</th>\n",
       "      <th>sale_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1102935</td>\n",
       "      <td>900019019</td>\n",
       "      <td>Alexandra Kundt</td>\n",
       "      <td>Senior Sales Associate</td>\n",
       "      <td>S Central East</td>\n",
       "      <td>2019-02-09 00:00:00</td>\n",
       "      <td>Retail</td>\n",
       "      <td>Individual</td>\n",
       "      <td>Skipton Fealty</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>Olive Oil</td>\n",
       "      <td>OO206</td>\n",
       "      <td>Chili Extra Virgin Olive Oil 2pk</td>\n",
       "      <td>3</td>\n",
       "      <td>45.0</td>\n",
       "      <td>135.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1102976</td>\n",
       "      <td>900019019</td>\n",
       "      <td>Alexandra Kundt</td>\n",
       "      <td>Senior Sales Associate</td>\n",
       "      <td>S Central East</td>\n",
       "      <td>2019-02-15 00:00:00</td>\n",
       "      <td>Retail</td>\n",
       "      <td>Individual</td>\n",
       "      <td>Lanni D'Ambrogi</td>\n",
       "      <td>Missouri</td>\n",
       "      <td>Gift Basket</td>\n",
       "      <td>GB301</td>\n",
       "      <td>Scented Olive Oil Candle Gift Basket</td>\n",
       "      <td>1</td>\n",
       "      <td>19.5</td>\n",
       "      <td>19.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_number  employee_id    employee_name               job_title  \\\n",
       "0       1102935    900019019  Alexandra Kundt  Senior Sales Associate   \n",
       "1       1102976    900019019  Alexandra Kundt  Senior Sales Associate   \n",
       "\n",
       "     sales_region           order_date order_type customer_type  \\\n",
       "0  S Central East  2019-02-09 00:00:00     Retail    Individual   \n",
       "1  S Central East  2019-02-15 00:00:00     Retail    Individual   \n",
       "\n",
       "     customer_name customer_state product_category product_number  \\\n",
       "0   Skipton Fealty       Arkansas        Olive Oil          OO206   \n",
       "1  Lanni D'Ambrogi       Missouri      Gift Basket          GB301   \n",
       "\n",
       "                           produce_name  quantity  unit_price  sale_price  \n",
       "0      Chili Extra Virgin Olive Oil 2pk         3        45.0       135.0  \n",
       "1  Scented Olive Oil Candle Gift Basket         1        19.5        19.5  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "sales_data = pd.read_csv(\n",
    "    \"sales_data.csv\",\n",
    "    parse_dates=[\"order_date\"],\n",
    "    dayfirst=True,\n",
    ").convert_dtypes(dtype_backend=\"pyarrow\")\n",
    "\n",
    "sales_data.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "239f06eb-db0b-45bb-9825-9192797bc55d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "order_number                int64[pyarrow]\n",
       "employee_id                 int64[pyarrow]\n",
       "employee_name              string[pyarrow]\n",
       "job_title                  string[pyarrow]\n",
       "sales_region               string[pyarrow]\n",
       "order_date          timestamp[ns][pyarrow]\n",
       "order_type                 string[pyarrow]\n",
       "customer_type              string[pyarrow]\n",
       "customer_name              string[pyarrow]\n",
       "customer_state             string[pyarrow]\n",
       "product_category           string[pyarrow]\n",
       "product_number             string[pyarrow]\n",
       "produce_name               string[pyarrow]\n",
       "quantity                    int64[pyarrow]\n",
       "unit_price                 double[pyarrow]\n",
       "sale_price                 double[pyarrow]\n",
       "dtype: object"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales_data.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "f2f57af3-d689-45d7-a017-3578895c5179",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5130 entries, 0 to 5129\n",
      "Data columns (total 16 columns):\n",
      " #   Column            Non-Null Count  Dtype                 \n",
      "---  ------            --------------  -----                 \n",
      " 0   order_number      5130 non-null   int64[pyarrow]        \n",
      " 1   employee_id       5130 non-null   int64[pyarrow]        \n",
      " 2   employee_name     5130 non-null   string[pyarrow]       \n",
      " 3   job_title         5130 non-null   string[pyarrow]       \n",
      " 4   sales_region      5130 non-null   string[pyarrow]       \n",
      " 5   order_date        5130 non-null   timestamp[ns][pyarrow]\n",
      " 6   order_type        5130 non-null   string[pyarrow]       \n",
      " 7   customer_type     5130 non-null   string[pyarrow]       \n",
      " 8   customer_name     5130 non-null   string[pyarrow]       \n",
      " 9   customer_state    5130 non-null   string[pyarrow]       \n",
      " 10  product_category  5130 non-null   string[pyarrow]       \n",
      " 11  product_number    5130 non-null   string[pyarrow]       \n",
      " 12  produce_name      5130 non-null   string[pyarrow]       \n",
      " 13  quantity          5130 non-null   int64[pyarrow]        \n",
      " 14  unit_price        5130 non-null   double[pyarrow]       \n",
      " 15  sale_price        5130 non-null   double[pyarrow]       \n",
      "dtypes: double[pyarrow](2), int64[pyarrow](3), string[pyarrow](10), timestamp[ns][pyarrow](1)\n",
      "memory usage: 1.1 MB\n"
     ]
    }
   ],
   "source": [
    "sales_data.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6ef0b664-c295-41d2-89c2-33086caee992",
   "metadata": {},
   "source": [
    "# How to create your first pivot table with pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "b00cb1ff-b91f-49df-a235-47863b599f06",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>order_type</th>\n",
       "      <th>Retail</th>\n",
       "      <th>Wholesale</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sales_region</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Central East</th>\n",
       "      <td>$102,613.51</td>\n",
       "      <td>$149,137.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>N Central East</th>\n",
       "      <td>$117,451.69</td>\n",
       "      <td>$152,446.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>N Central West</th>\n",
       "      <td>$10,006.42</td>\n",
       "      <td>$1,731.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Northeast</th>\n",
       "      <td>$84,078.95</td>\n",
       "      <td>$127,423.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Northwest</th>\n",
       "      <td>$34,565.62</td>\n",
       "      <td>$33,240.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S Central East</th>\n",
       "      <td>$130,742.32</td>\n",
       "      <td>$208,945.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S Central West</th>\n",
       "      <td>$54,681.80</td>\n",
       "      <td>$51,051.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Southeast</th>\n",
       "      <td>$96,310.12</td>\n",
       "      <td>$127,554.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Southwest</th>\n",
       "      <td>$104,743.52</td>\n",
       "      <td>$121,977.20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "order_type          Retail   Wholesale\n",
       "sales_region                          \n",
       "Central East   $102,613.51 $149,137.89\n",
       "N Central East $117,451.69 $152,446.42\n",
       "N Central West  $10,006.42   $1,731.50\n",
       "Northeast       $84,078.95 $127,423.36\n",
       "Northwest       $34,565.62  $33,240.12\n",
       "S Central East $130,742.32 $208,945.73\n",
       "S Central West  $54,681.80  $51,051.03\n",
       "Southeast       $96,310.12 $127,554.60\n",
       "Southwest      $104,743.52 $121,977.20"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.set_option(\"display.float_format\", \"${:,.2f}\".format)\n",
    "\n",
    "sales_data.pivot_table(\n",
    "    values=\"sale_price\",\n",
    "    index=\"sales_region\",\n",
    "    columns=\"order_type\",\n",
    "    aggfunc=\"sum\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "df0d98f9-8c4e-4027-82d3-eca1b3e549f8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>order_type</th>\n",
       "      <th>Retail</th>\n",
       "      <th>Wholesale</th>\n",
       "      <th>Totals</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sales_region</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Central East</th>\n",
       "      <td>$102,613.51</td>\n",
       "      <td>$149,137.89</td>\n",
       "      <td>$251,751.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>N Central East</th>\n",
       "      <td>$117,451.69</td>\n",
       "      <td>$152,446.42</td>\n",
       "      <td>$269,898.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>N Central West</th>\n",
       "      <td>$10,006.42</td>\n",
       "      <td>$1,731.50</td>\n",
       "      <td>$11,737.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Northeast</th>\n",
       "      <td>$84,078.95</td>\n",
       "      <td>$127,423.36</td>\n",
       "      <td>$211,502.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Northwest</th>\n",
       "      <td>$34,565.62</td>\n",
       "      <td>$33,240.12</td>\n",
       "      <td>$67,805.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S Central East</th>\n",
       "      <td>$130,742.32</td>\n",
       "      <td>$208,945.73</td>\n",
       "      <td>$339,688.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S Central West</th>\n",
       "      <td>$54,681.80</td>\n",
       "      <td>$51,051.03</td>\n",
       "      <td>$105,732.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Southeast</th>\n",
       "      <td>$96,310.12</td>\n",
       "      <td>$127,554.60</td>\n",
       "      <td>$223,864.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Southwest</th>\n",
       "      <td>$104,743.52</td>\n",
       "      <td>$121,977.20</td>\n",
       "      <td>$226,720.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Totals</th>\n",
       "      <td>$735,193.95</td>\n",
       "      <td>$973,507.85</td>\n",
       "      <td>$1,708,701.80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "order_type          Retail   Wholesale        Totals\n",
       "sales_region                                        \n",
       "Central East   $102,613.51 $149,137.89   $251,751.40\n",
       "N Central East $117,451.69 $152,446.42   $269,898.11\n",
       "N Central West  $10,006.42   $1,731.50    $11,737.92\n",
       "Northeast       $84,078.95 $127,423.36   $211,502.31\n",
       "Northwest       $34,565.62  $33,240.12    $67,805.74\n",
       "S Central East $130,742.32 $208,945.73   $339,688.05\n",
       "S Central West  $54,681.80  $51,051.03   $105,732.83\n",
       "Southeast       $96,310.12 $127,554.60   $223,864.72\n",
       "Southwest      $104,743.52 $121,977.20   $226,720.72\n",
       "Totals         $735,193.95 $973,507.85 $1,708,701.80"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.set_option(\"display.float_format\", \"${:,.2f}\".format)\n",
    "\n",
    "sales_data.pivot_table(\n",
    "    values=\"sale_price\",\n",
    "    index=\"sales_region\",\n",
    "    columns=\"order_type\",\n",
    "    aggfunc=\"sum\",\n",
    "    margins=True,\n",
    "    margins_name=\"Totals\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5e91d372-b055-4447-a8f8-fe07962867a6",
   "metadata": {},
   "source": [
    "# Including sub-columns In your pivot table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "b888e088-1f2c-465a-a2bc-b8832c137e62",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th>customer_type</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Business</th>\n",
       "      <th>Individual</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>order_type</th>\n",
       "      <th>Retail</th>\n",
       "      <th>Wholesale</th>\n",
       "      <th>Retail</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>customer_state</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Alabama</th>\n",
       "      <td>$362.67</td>\n",
       "      <td>$762.73</td>\n",
       "      <td>$137.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Alaska</th>\n",
       "      <td>$295.33</td>\n",
       "      <td>$799.83</td>\n",
       "      <td>$137.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arizona</th>\n",
       "      <td>$407.50</td>\n",
       "      <td>$1,228.52</td>\n",
       "      <td>$194.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arkansas</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>$1,251.25</td>\n",
       "      <td>$181.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>California</th>\n",
       "      <td>$110.53</td>\n",
       "      <td>$1,198.89</td>\n",
       "      <td>$170.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Colorado</th>\n",
       "      <td>$242.72</td>\n",
       "      <td>$1,215.93</td>\n",
       "      <td>$187.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Connecticut</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>$1,431.64</td>\n",
       "      <td>$154.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Delaware</th>\n",
       "      <td>$47.17</td>\n",
       "      <td>$1,092.00</td>\n",
       "      <td>$216.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>District of Columbia</th>\n",
       "      <td>$13.77</td>\n",
       "      <td>$891.66</td>\n",
       "      <td>$153.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Florida</th>\n",
       "      <td>$199.50</td>\n",
       "      <td>$1,362.92</td>\n",
       "      <td>$162.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Georgia</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>$1,537.47</td>\n",
       "      <td>$147.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hawaii</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>$114.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Idaho</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>$183.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Illinois</th>\n",
       "      <td>$164.80</td>\n",
       "      <td>$1,382.59</td>\n",
       "      <td>$140.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Indiana</th>\n",
       "      <td>$253.00</td>\n",
       "      <td>$1,040.65</td>\n",
       "      <td>$163.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Iowa</th>\n",
       "      <td>$298.22</td>\n",
       "      <td>$1,561.50</td>\n",
       "      <td>$192.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kansas</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>$1,168.13</td>\n",
       "      <td>$125.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kentucky</th>\n",
       "      <td>$84.74</td>\n",
       "      <td>$1,465.40</td>\n",
       "      <td>$175.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Louisiana</th>\n",
       "      <td>$329.94</td>\n",
       "      <td>$1,404.00</td>\n",
       "      <td>$152.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Maryland</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>$1,495.25</td>\n",
       "      <td>$200.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Massachusetts</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>$1,683.00</td>\n",
       "      <td>$186.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Michigan</th>\n",
       "      <td>$260.75</td>\n",
       "      <td>$1,213.50</td>\n",
       "      <td>$205.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Minnesota</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>$1,291.77</td>\n",
       "      <td>$166.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mississippi</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>$134.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Missouri</th>\n",
       "      <td>$178.33</td>\n",
       "      <td>$1,433.96</td>\n",
       "      <td>$161.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Montana</th>\n",
       "      <td>$47.83</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>$182.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nebraska</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>$577.17</td>\n",
       "      <td>$176.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nevada</th>\n",
       "      <td>$17.48</td>\n",
       "      <td>$1,321.74</td>\n",
       "      <td>$154.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New Hampshire</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>$102.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New Jersey</th>\n",
       "      <td>$287.50</td>\n",
       "      <td>$1,809.00</td>\n",
       "      <td>$188.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New Mexico</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>$627.00</td>\n",
       "      <td>$165.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New York</th>\n",
       "      <td>$125.55</td>\n",
       "      <td>$1,254.17</td>\n",
       "      <td>$159.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>North Carolina</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>$1,031.40</td>\n",
       "      <td>$159.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>North Dakota</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>$136.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ohio</th>\n",
       "      <td>$239.99</td>\n",
       "      <td>$1,183.46</td>\n",
       "      <td>$160.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Oklahoma</th>\n",
       "      <td>$27.32</td>\n",
       "      <td>$936.16</td>\n",
       "      <td>$155.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Oregon</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>$1,172.75</td>\n",
       "      <td>$252.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pennsylvania</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>$1,576.11</td>\n",
       "      <td>$159.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South Carolina</th>\n",
       "      <td>$79.66</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>$246.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South Dakota</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>$126.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tennessee</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>$1,321.14</td>\n",
       "      <td>$187.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Texas</th>\n",
       "      <td>$290.92</td>\n",
       "      <td>$1,350.58</td>\n",
       "      <td>$169.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Utah</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>$1,379.90</td>\n",
       "      <td>$145.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vermont</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>$1,603.33</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Virginia</th>\n",
       "      <td>$203.79</td>\n",
       "      <td>$1,456.01</td>\n",
       "      <td>$192.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Washington</th>\n",
       "      <td>$148.87</td>\n",
       "      <td>$933.91</td>\n",
       "      <td>$166.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>West Virginia</th>\n",
       "      <td>$195.99</td>\n",
       "      <td>$1,771.71</td>\n",
       "      <td>$163.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wisconsin</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>$1,412.21</td>\n",
       "      <td>$167.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wyoming</th>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>$36.73</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "customer_type        Business           Individual\n",
       "order_type             Retail Wholesale     Retail\n",
       "customer_state                                    \n",
       "Alabama               $362.67   $762.73    $137.47\n",
       "Alaska                $295.33   $799.83    $137.18\n",
       "Arizona               $407.50 $1,228.52    $194.46\n",
       "Arkansas                 <NA> $1,251.25    $181.65\n",
       "California            $110.53 $1,198.89    $170.94\n",
       "Colorado              $242.72 $1,215.93    $187.04\n",
       "Connecticut              <NA> $1,431.64    $154.19\n",
       "Delaware               $47.17 $1,092.00    $216.00\n",
       "District of Columbia   $13.77   $891.66    $153.79\n",
       "Florida               $199.50 $1,362.92    $162.78\n",
       "Georgia                  <NA> $1,537.47    $147.48\n",
       "Hawaii                   <NA>      <NA>    $114.51\n",
       "Idaho                    <NA>      <NA>    $183.02\n",
       "Illinois              $164.80 $1,382.59    $140.05\n",
       "Indiana               $253.00 $1,040.65    $163.95\n",
       "Iowa                  $298.22 $1,561.50    $192.89\n",
       "Kansas                   <NA> $1,168.13    $125.07\n",
       "Kentucky               $84.74 $1,465.40    $175.51\n",
       "Louisiana             $329.94 $1,404.00    $152.95\n",
       "Maryland                 <NA> $1,495.25    $200.06\n",
       "Massachusetts            <NA> $1,683.00    $186.01\n",
       "Michigan              $260.75 $1,213.50    $205.01\n",
       "Minnesota                <NA> $1,291.77    $166.84\n",
       "Mississippi              <NA>      <NA>    $134.93\n",
       "Missouri              $178.33 $1,433.96    $161.46\n",
       "Montana                $47.83      <NA>    $182.91\n",
       "Nebraska                 <NA>   $577.17    $176.36\n",
       "Nevada                 $17.48 $1,321.74    $154.73\n",
       "New Hampshire            <NA>      <NA>    $102.64\n",
       "New Jersey            $287.50 $1,809.00    $188.06\n",
       "New Mexico               <NA>   $627.00    $165.91\n",
       "New York              $125.55 $1,254.17    $159.52\n",
       "North Carolina           <NA> $1,031.40    $159.53\n",
       "North Dakota             <NA>      <NA>    $136.98\n",
       "Ohio                  $239.99 $1,183.46    $160.85\n",
       "Oklahoma               $27.32   $936.16    $155.46\n",
       "Oregon                   <NA> $1,172.75    $252.58\n",
       "Pennsylvania             <NA> $1,576.11    $159.94\n",
       "South Carolina         $79.66      <NA>    $246.89\n",
       "South Dakota             <NA>      <NA>    $126.96\n",
       "Tennessee                <NA> $1,321.14    $187.80\n",
       "Texas                 $290.92 $1,350.58    $169.27\n",
       "Utah                     <NA> $1,379.90    $145.42\n",
       "Vermont                  <NA> $1,603.33       <NA>\n",
       "Virginia              $203.79 $1,456.01    $192.13\n",
       "Washington            $148.87   $933.91    $166.08\n",
       "West Virginia         $195.99 $1,771.71    $163.95\n",
       "Wisconsin                <NA> $1,412.21    $167.19\n",
       "Wyoming                  <NA>      <NA>     $36.73"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "pd.set_option(\"display.float_format\", \"${:,.2f}\".format)\n",
    "\n",
    "sales_data.pivot_table(\n",
    "    values=\"sale_price\",\n",
    "    index=\"customer_state\",\n",
    "    columns=[\"customer_type\", \"order_type\"],\n",
    "    aggfunc=\"mean\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5479b18f-1817-4578-9ec9-e4dfaa088537",
   "metadata": {},
   "source": [
    "# Including Sub-Rows In Your Pivot Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "29ebe612-ea62-41a6-bf85-19b78244c8a0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>product_category</th>\n",
       "      <th>Bath products</th>\n",
       "      <th>Gift Basket</th>\n",
       "      <th>Olive Oil</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>customer_type</th>\n",
       "      <th>order_type</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Business</th>\n",
       "      <th>Retail</th>\n",
       "      <td>$1,060.87</td>\n",
       "      <td>$3,678.50</td>\n",
       "      <td>$23,835.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wholesale</th>\n",
       "      <td>$6,024.60</td>\n",
       "      <td>$18,787.50</td>\n",
       "      <td>$948,695.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Individual</th>\n",
       "      <th>Retail</th>\n",
       "      <td>$32,711.58</td>\n",
       "      <td>$113,275.00</td>\n",
       "      <td>$560,633.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "product_category          Bath products  Gift Basket   Olive Oil\n",
       "customer_type order_type                                        \n",
       "Business      Retail          $1,060.87    $3,678.50  $23,835.00\n",
       "              Wholesale       $6,024.60   $18,787.50 $948,695.75\n",
       "Individual    Retail         $32,711.58  $113,275.00 $560,633.00"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.set_option(\"display.float_format\", \"${:,.2f}\".format)\n",
    "\n",
    "sales_data.pivot_table(\n",
    "    values=\"sale_price\",\n",
    "    index=[\"customer_type\", \"order_type\"],\n",
    "    columns=\"product_category\",\n",
    "    aggfunc=\"sum\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c19c30fc-3253-446a-b2e4-77dd4cc53c36",
   "metadata": {},
   "source": [
    "# Calculating multiple values in your pivot table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "a59d0173-87a2-4960-9ece-461820392363",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>quantity</th>\n",
       "      <th>sale_price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sales_region</th>\n",
       "      <th>product_category</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Central East</th>\n",
       "      <th>Bath products</th>\n",
       "      <td>543</td>\n",
       "      <td>$5,315.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gift Basket</th>\n",
       "      <td>267</td>\n",
       "      <td>$16,309.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Olive Oil</th>\n",
       "      <td>1497</td>\n",
       "      <td>$230,126.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">N Central East</th>\n",
       "      <th>Bath products</th>\n",
       "      <td>721</td>\n",
       "      <td>$6,905.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gift Basket</th>\n",
       "      <td>362</td>\n",
       "      <td>$21,533.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Olive Oil</th>\n",
       "      <td>1648</td>\n",
       "      <td>$241,459.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">N Central West</th>\n",
       "      <th>Bath products</th>\n",
       "      <td>63</td>\n",
       "      <td>$690.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gift Basket</th>\n",
       "      <td>26</td>\n",
       "      <td>$2,023.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Olive Oil</th>\n",
       "      <td>87</td>\n",
       "      <td>$9,023.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Northeast</th>\n",
       "      <th>Bath products</th>\n",
       "      <td>423</td>\n",
       "      <td>$4,267.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gift Basket</th>\n",
       "      <td>254</td>\n",
       "      <td>$16,985.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Olive Oil</th>\n",
       "      <td>1259</td>\n",
       "      <td>$190,249.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Northwest</th>\n",
       "      <th>Bath products</th>\n",
       "      <td>207</td>\n",
       "      <td>$1,909.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gift Basket</th>\n",
       "      <td>111</td>\n",
       "      <td>$6,115.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Olive Oil</th>\n",
       "      <td>430</td>\n",
       "      <td>$59,781.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">S Central East</th>\n",
       "      <th>Bath products</th>\n",
       "      <td>717</td>\n",
       "      <td>$7,458.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gift Basket</th>\n",
       "      <td>445</td>\n",
       "      <td>$25,524.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Olive Oil</th>\n",
       "      <td>1945</td>\n",
       "      <td>$306,705.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">S Central West</th>\n",
       "      <th>Bath products</th>\n",
       "      <td>223</td>\n",
       "      <td>$2,250.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gift Basket</th>\n",
       "      <td>133</td>\n",
       "      <td>$9,118.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Olive Oil</th>\n",
       "      <td>623</td>\n",
       "      <td>$94,364.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Southeast</th>\n",
       "      <th>Bath products</th>\n",
       "      <td>492</td>\n",
       "      <td>$5,101.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gift Basket</th>\n",
       "      <td>302</td>\n",
       "      <td>$18,900.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Olive Oil</th>\n",
       "      <td>1450</td>\n",
       "      <td>$199,862.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Southwest</th>\n",
       "      <th>Bath products</th>\n",
       "      <td>587</td>\n",
       "      <td>$5,897.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gift Basket</th>\n",
       "      <td>325</td>\n",
       "      <td>$19,232.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Olive Oil</th>\n",
       "      <td>1383</td>\n",
       "      <td>$201,590.75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 quantity  sale_price\n",
       "sales_region   product_category                      \n",
       "Central East   Bath products          543   $5,315.40\n",
       "               Gift Basket            267  $16,309.50\n",
       "               Olive Oil             1497 $230,126.50\n",
       "N Central East Bath products          721   $6,905.36\n",
       "               Gift Basket            362  $21,533.00\n",
       "               Olive Oil             1648 $241,459.75\n",
       "N Central West Bath products           63     $690.92\n",
       "               Gift Basket             26   $2,023.50\n",
       "               Olive Oil               87   $9,023.50\n",
       "Northeast      Bath products          423   $4,267.56\n",
       "               Gift Basket            254  $16,985.00\n",
       "               Olive Oil             1259 $190,249.75\n",
       "Northwest      Bath products          207   $1,909.24\n",
       "               Gift Basket            111   $6,115.00\n",
       "               Olive Oil              430  $59,781.50\n",
       "S Central East Bath products          717   $7,458.55\n",
       "               Gift Basket            445  $25,524.50\n",
       "               Olive Oil             1945 $306,705.00\n",
       "S Central West Bath products          223   $2,250.08\n",
       "               Gift Basket            133   $9,118.00\n",
       "               Olive Oil              623  $94,364.75\n",
       "Southeast      Bath products          492   $5,101.97\n",
       "               Gift Basket            302  $18,900.50\n",
       "               Olive Oil             1450 $199,862.25\n",
       "Southwest      Bath products          587   $5,897.97\n",
       "               Gift Basket            325  $19,232.00\n",
       "               Olive Oil             1383 $201,590.75"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "pd.set_option(\"display.float_format\", \"${:,.2f}\".format)\n",
    "\n",
    "sales_data.pivot_table(\n",
    "    index=[\"sales_region\", \"product_category\"],\n",
    "    values=[\"sale_price\", \"quantity\"],\n",
    "    aggfunc=\"sum\",\n",
    "    fill_value=0,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "138491eb-7082-47dd-a48d-993ad3219214",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>sale_price</th>\n",
       "      <th>quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sales_region</th>\n",
       "      <th>product_category</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Central East</th>\n",
       "      <th>Bath products</th>\n",
       "      <td>$5,315.40</td>\n",
       "      <td>543</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gift Basket</th>\n",
       "      <td>$16,309.50</td>\n",
       "      <td>267</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Olive Oil</th>\n",
       "      <td>$230,126.50</td>\n",
       "      <td>1497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">N Central East</th>\n",
       "      <th>Bath products</th>\n",
       "      <td>$6,905.36</td>\n",
       "      <td>721</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gift Basket</th>\n",
       "      <td>$21,533.00</td>\n",
       "      <td>362</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Olive Oil</th>\n",
       "      <td>$241,459.75</td>\n",
       "      <td>1648</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">N Central West</th>\n",
       "      <th>Bath products</th>\n",
       "      <td>$690.92</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gift Basket</th>\n",
       "      <td>$2,023.50</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Olive Oil</th>\n",
       "      <td>$9,023.50</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Northeast</th>\n",
       "      <th>Bath products</th>\n",
       "      <td>$4,267.56</td>\n",
       "      <td>423</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gift Basket</th>\n",
       "      <td>$16,985.00</td>\n",
       "      <td>254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Olive Oil</th>\n",
       "      <td>$190,249.75</td>\n",
       "      <td>1259</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Northwest</th>\n",
       "      <th>Bath products</th>\n",
       "      <td>$1,909.24</td>\n",
       "      <td>207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gift Basket</th>\n",
       "      <td>$6,115.00</td>\n",
       "      <td>111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Olive Oil</th>\n",
       "      <td>$59,781.50</td>\n",
       "      <td>430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">S Central East</th>\n",
       "      <th>Bath products</th>\n",
       "      <td>$7,458.55</td>\n",
       "      <td>717</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gift Basket</th>\n",
       "      <td>$25,524.50</td>\n",
       "      <td>445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Olive Oil</th>\n",
       "      <td>$306,705.00</td>\n",
       "      <td>1945</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">S Central West</th>\n",
       "      <th>Bath products</th>\n",
       "      <td>$2,250.08</td>\n",
       "      <td>223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gift Basket</th>\n",
       "      <td>$9,118.00</td>\n",
       "      <td>133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Olive Oil</th>\n",
       "      <td>$94,364.75</td>\n",
       "      <td>623</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Southeast</th>\n",
       "      <th>Bath products</th>\n",
       "      <td>$5,101.97</td>\n",
       "      <td>492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gift Basket</th>\n",
       "      <td>$18,900.50</td>\n",
       "      <td>302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Olive Oil</th>\n",
       "      <td>$199,862.25</td>\n",
       "      <td>1450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Southwest</th>\n",
       "      <th>Bath products</th>\n",
       "      <td>$5,897.97</td>\n",
       "      <td>587</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gift Basket</th>\n",
       "      <td>$19,232.00</td>\n",
       "      <td>325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Olive Oil</th>\n",
       "      <td>$201,590.75</td>\n",
       "      <td>1383</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 sale_price  quantity\n",
       "sales_region   product_category                      \n",
       "Central East   Bath products      $5,315.40       543\n",
       "               Gift Basket       $16,309.50       267\n",
       "               Olive Oil        $230,126.50      1497\n",
       "N Central East Bath products      $6,905.36       721\n",
       "               Gift Basket       $21,533.00       362\n",
       "               Olive Oil        $241,459.75      1648\n",
       "N Central West Bath products        $690.92        63\n",
       "               Gift Basket        $2,023.50        26\n",
       "               Olive Oil          $9,023.50        87\n",
       "Northeast      Bath products      $4,267.56       423\n",
       "               Gift Basket       $16,985.00       254\n",
       "               Olive Oil        $190,249.75      1259\n",
       "Northwest      Bath products      $1,909.24       207\n",
       "               Gift Basket        $6,115.00       111\n",
       "               Olive Oil         $59,781.50       430\n",
       "S Central East Bath products      $7,458.55       717\n",
       "               Gift Basket       $25,524.50       445\n",
       "               Olive Oil        $306,705.00      1945\n",
       "S Central West Bath products      $2,250.08       223\n",
       "               Gift Basket        $9,118.00       133\n",
       "               Olive Oil         $94,364.75       623\n",
       "Southeast      Bath products      $5,101.97       492\n",
       "               Gift Basket       $18,900.50       302\n",
       "               Olive Oil        $199,862.25      1450\n",
       "Southwest      Bath products      $5,897.97       587\n",
       "               Gift Basket       $19,232.00       325\n",
       "               Olive Oil        $201,590.75      1383"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# This example ensures column order matches the order in the values parameter.\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "pd.set_option(\"display.float_format\", \"${:,.2f}\".format)\n",
    "\n",
    "sales_data.pivot_table(\n",
    "    index=[\"sales_region\", \"product_category\"],\n",
    "    values=[\"sale_price\", \"quantity\"],\n",
    "    aggfunc=\"sum\",\n",
    "    fill_value=0,\n",
    ").loc[:, [\"sale_price\", \"quantity\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c323235b-3479-469e-b6cb-ee08615897d5",
   "metadata": {},
   "source": [
    "# Performing more advanced aggregations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "d2a2979b-694c-4880-aea7-9a1e8cf4573d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">max</th>\n",
       "      <th colspan=\"2\" halign=\"left\">min</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">sale_price</th>\n",
       "      <th colspan=\"2\" halign=\"left\">sale_price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>customer_type</th>\n",
       "      <th>Business</th>\n",
       "      <th>Individual</th>\n",
       "      <th>Business</th>\n",
       "      <th>Individual</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>product_category</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Bath products</th>\n",
       "      <td>$300.00</td>\n",
       "      <td>$120.00</td>\n",
       "      <td>$5.99</td>\n",
       "      <td>$5.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gift Basket</th>\n",
       "      <td>$1,150.00</td>\n",
       "      <td>$460.00</td>\n",
       "      <td>$27.00</td>\n",
       "      <td>$19.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Olive Oil</th>\n",
       "      <td>$3,276.00</td>\n",
       "      <td>$936.00</td>\n",
       "      <td>$16.75</td>\n",
       "      <td>$16.75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        max                   min           \n",
       "                 sale_price            sale_price           \n",
       "customer_type      Business Individual   Business Individual\n",
       "product_category                                            \n",
       "Bath products       $300.00    $120.00      $5.99      $5.99\n",
       "Gift Basket       $1,150.00    $460.00     $27.00     $19.50\n",
       "Olive Oil         $3,276.00    $936.00     $16.75     $16.75"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.set_option(\"display.float_format\", \"${:,.2f}\".format)\n",
    "\n",
    "sales_data.pivot_table(\n",
    "    values=[\"sale_price\"],\n",
    "    index=\"product_category\",\n",
    "    columns=\"customer_type\",\n",
    "    aggfunc=[\"max\", \"min\"],\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "45b1a6d7-1b72-458f-a25d-a3c1c7109a46",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">quantity</th>\n",
       "      <th colspan=\"2\" halign=\"left\">sale_price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>customer_type</th>\n",
       "      <th>Business</th>\n",
       "      <th>Individual</th>\n",
       "      <th>Business</th>\n",
       "      <th>Individual</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>product_category</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Bath products</th>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>$53.27</td>\n",
       "      <td>$25.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gift Basket</th>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>$335.31</td>\n",
       "      <td>$156.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Olive Oil</th>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>$1,385.37</td>\n",
       "      <td>$250.06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 quantity            sale_price           \n",
       "customer_type    Business Individual   Business Individual\n",
       "product_category                                          \n",
       "Bath products          14          4     $53.27     $25.94\n",
       "Gift Basket            14          4    $335.31    $156.24\n",
       "Olive Oil              14          4  $1,385.37    $250.06"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.set_option(\"display.float_format\", \"${:,.2f}\".format)\n",
    "\n",
    "sales_data.pivot_table(\n",
    "    values=[\"sale_price\", \"quantity\"],\n",
    "    index=[\"product_category\"],\n",
    "    columns=\"customer_type\",\n",
    "    aggfunc={\"sale_price\": \"mean\", \"quantity\": \"max\"},\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "93db010a-71c9-49d0-babb-ce99e0d8f801",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>employee_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sales_region</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Central East</th>\n",
       "      <td>697</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>N Central East</th>\n",
       "      <td>832</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>N Central West</th>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Northeast</th>\n",
       "      <td>604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Northwest</th>\n",
       "      <td>230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S Central East</th>\n",
       "      <td>941</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S Central West</th>\n",
       "      <td>331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Southeast</th>\n",
       "      <td>694</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Southwest</th>\n",
       "      <td>731</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                employee_id\n",
       "sales_region               \n",
       "Central East            697\n",
       "N Central East          832\n",
       "N Central West           70\n",
       "Northeast               604\n",
       "Northwest               230\n",
       "S Central East          941\n",
       "S Central West          331\n",
       "Southeast               694\n",
       "Southwest               731"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales_data.pivot_table(\n",
    "    values=\"employee_id\", index=\"sales_region\", aggfunc=\"count\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "57f47424-fddf-4ab7-bff4-b3f973cb8565",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>employee_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sales_region</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Central East</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>N Central East</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>N Central West</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Northeast</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Northwest</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S Central East</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S Central West</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Southeast</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Southwest</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                employee_id\n",
       "sales_region               \n",
       "Central East              6\n",
       "N Central East            6\n",
       "N Central West            1\n",
       "Northeast                 4\n",
       "Northwest                 4\n",
       "S Central East            6\n",
       "S Central West            5\n",
       "Southeast                 6\n",
       "Southwest                 6"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def count_unique(values):\n",
    "    return len(values.unique())\n",
    "\n",
    "\n",
    "sales_data.pivot_table(\n",
    "    values=\"employee_id\", index=[\"sales_region\"], aggfunc=count_unique\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a17481c1-eacb-4c6f-9b6d-889ade86f62f",
   "metadata": {},
   "source": [
    "# Using `.groupby()` and `crosstab()` for Aggregation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "2a483cf2-1bf0-48a6-b547-e537df15ceb9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>min</th>\n",
       "      <th>mean</th>\n",
       "      <th>max</th>\n",
       "      <th>std</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>sale_price</th>\n",
       "      <th>sale_price</th>\n",
       "      <th>sale_price</th>\n",
       "      <th>sale_price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>product_category</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Bath products</th>\n",
       "      <td>$5.99</td>\n",
       "      <td>$28.55</td>\n",
       "      <td>$300.00</td>\n",
       "      <td>$23.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gift Basket</th>\n",
       "      <td>$19.50</td>\n",
       "      <td>$171.39</td>\n",
       "      <td>$1,150.00</td>\n",
       "      <td>$131.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Olive Oil</th>\n",
       "      <td>$16.75</td>\n",
       "      <td>$520.78</td>\n",
       "      <td>$3,276.00</td>\n",
       "      <td>$721.49</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        min       mean        max        std\n",
       "                 sale_price sale_price sale_price sale_price\n",
       "product_category                                            \n",
       "Bath products         $5.99     $28.55    $300.00     $23.98\n",
       "Gift Basket          $19.50    $171.39  $1,150.00    $131.64\n",
       "Olive Oil            $16.75    $520.78  $3,276.00    $721.49"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales_data.pivot_table(\n",
    "    values=\"sale_price\",\n",
    "    index=\"product_category\",\n",
    "    aggfunc=[\"min\", \"mean\", \"max\", \"std\"],\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "b0031b51-22ab-4c64-bfd2-81de360db501",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>low_price</th>\n",
       "      <th>average_price</th>\n",
       "      <th>high_price</th>\n",
       "      <th>standard_deviation</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>product_category</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Bath products</th>\n",
       "      <td>$5.99</td>\n",
       "      <td>$28.55</td>\n",
       "      <td>$300.00</td>\n",
       "      <td>$23.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gift Basket</th>\n",
       "      <td>$19.50</td>\n",
       "      <td>$171.39</td>\n",
       "      <td>$1,150.00</td>\n",
       "      <td>$131.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Olive Oil</th>\n",
       "      <td>$16.75</td>\n",
       "      <td>$520.78</td>\n",
       "      <td>$3,276.00</td>\n",
       "      <td>$721.49</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  low_price  average_price  high_price  standard_deviation\n",
       "product_category                                                          \n",
       "Bath products         $5.99         $28.55     $300.00              $23.98\n",
       "Gift Basket          $19.50        $171.39   $1,150.00             $131.64\n",
       "Olive Oil            $16.75        $520.78   $3,276.00             $721.49"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(\n",
    "    sales_data.groupby(\"product_category\").agg(\n",
    "        low_price=(\"sale_price\", \"min\"),\n",
    "        average_price=(\"sale_price\", \"mean\"),\n",
    "        high_price=(\"sale_price\", \"max\"),\n",
    "        standard_deviation=(\"sale_price\", \"std\"),\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "ef30559b-8be1-4eee-b644-5cc66d12f112",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>sales_region</th>\n",
       "      <th>Central East</th>\n",
       "      <th>N Central East</th>\n",
       "      <th>N Central West</th>\n",
       "      <th>Northeast</th>\n",
       "      <th>Northwest</th>\n",
       "      <th>S Central East</th>\n",
       "      <th>S Central West</th>\n",
       "      <th>Southeast</th>\n",
       "      <th>Southwest</th>\n",
       "      <th>Totals</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>job_title</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Sales Associate</th>\n",
       "      <td>0</td>\n",
       "      <td>132</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>87</td>\n",
       "      <td>0</td>\n",
       "      <td>138</td>\n",
       "      <td>357</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sales Associate I</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>70</td>\n",
       "      <td>0</td>\n",
       "      <td>73</td>\n",
       "      <td>135</td>\n",
       "      <td>202</td>\n",
       "      <td>195</td>\n",
       "      <td>254</td>\n",
       "      <td>929</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sales Associate II</th>\n",
       "      <td>139</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>147</td>\n",
       "      <td>0</td>\n",
       "      <td>346</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>95</td>\n",
       "      <td>727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sales Associate III</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>127</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>231</td>\n",
       "      <td>0</td>\n",
       "      <td>358</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sales Associate IV</th>\n",
       "      <td>0</td>\n",
       "      <td>62</td>\n",
       "      <td>0</td>\n",
       "      <td>221</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>283</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sales Associate V</th>\n",
       "      <td>0</td>\n",
       "      <td>183</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sales Representative</th>\n",
       "      <td>105</td>\n",
       "      <td>180</td>\n",
       "      <td>0</td>\n",
       "      <td>109</td>\n",
       "      <td>56</td>\n",
       "      <td>176</td>\n",
       "      <td>42</td>\n",
       "      <td>268</td>\n",
       "      <td>107</td>\n",
       "      <td>1043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Senior Sales Associate</th>\n",
       "      <td>0</td>\n",
       "      <td>148</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>284</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>432</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Senior Sales Representative</th>\n",
       "      <td>453</td>\n",
       "      <td>127</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>137</td>\n",
       "      <td>818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Totals</th>\n",
       "      <td>697</td>\n",
       "      <td>832</td>\n",
       "      <td>70</td>\n",
       "      <td>604</td>\n",
       "      <td>230</td>\n",
       "      <td>941</td>\n",
       "      <td>331</td>\n",
       "      <td>694</td>\n",
       "      <td>731</td>\n",
       "      <td>5130</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "sales_region                 Central East  N Central East  N Central West  \\\n",
       "job_title                                                                   \n",
       "Sales Associate                         0             132               0   \n",
       "Sales Associate I                       0               0              70   \n",
       "Sales Associate II                    139               0               0   \n",
       "Sales Associate III                     0               0               0   \n",
       "Sales Associate IV                      0              62               0   \n",
       "Sales Associate V                       0             183               0   \n",
       "Sales Representative                  105             180               0   \n",
       "Senior Sales Associate                  0             148               0   \n",
       "Senior Sales Representative           453             127               0   \n",
       "Totals                                697             832              70   \n",
       "\n",
       "sales_region                 Northeast  Northwest  S Central East  \\\n",
       "job_title                                                           \n",
       "Sales Associate                      0          0               0   \n",
       "Sales Associate I                    0         73             135   \n",
       "Sales Associate II                 147          0             346   \n",
       "Sales Associate III                127          0               0   \n",
       "Sales Associate IV                 221          0               0   \n",
       "Sales Associate V                    0          0               0   \n",
       "Sales Representative               109         56             176   \n",
       "Senior Sales Associate               0          0             284   \n",
       "Senior Sales Representative          0        101               0   \n",
       "Totals                             604        230             941   \n",
       "\n",
       "sales_region                 S Central West  Southeast  Southwest  Totals  \n",
       "job_title                                                                  \n",
       "Sales Associate                          87          0        138     357  \n",
       "Sales Associate I                       202        195        254     929  \n",
       "Sales Associate II                        0          0         95     727  \n",
       "Sales Associate III                       0        231          0     358  \n",
       "Sales Associate IV                        0          0          0     283  \n",
       "Sales Associate V                         0          0          0     183  \n",
       "Sales Representative                     42        268        107    1043  \n",
       "Senior Sales Associate                    0          0          0     432  \n",
       "Senior Sales Representative               0          0        137     818  \n",
       "Totals                                  331        694        731    5130  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab(\n",
    "    index=sales_data.job_title,\n",
    "    columns=sales_data.sales_region,\n",
    "    margins=True,\n",
    "    margins_name=\"Totals\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "6f47c7ac-e889-412e-98c9-8742c7b9d4e1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>sales_region</th>\n",
       "      <th>Central East</th>\n",
       "      <th>N Central East</th>\n",
       "      <th>N Central West</th>\n",
       "      <th>Northeast</th>\n",
       "      <th>Northwest</th>\n",
       "      <th>S Central East</th>\n",
       "      <th>S Central West</th>\n",
       "      <th>Southeast</th>\n",
       "      <th>Southwest</th>\n",
       "      <th>Totals</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>job_title</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Sales Associate</th>\n",
       "      <td>0.00%</td>\n",
       "      <td>2.57%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>1.70%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>2.69%</td>\n",
       "      <td>6.96%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sales Associate I</th>\n",
       "      <td>0.00%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>1.36%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>1.42%</td>\n",
       "      <td>2.63%</td>\n",
       "      <td>3.94%</td>\n",
       "      <td>3.80%</td>\n",
       "      <td>4.95%</td>\n",
       "      <td>18.11%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sales Associate II</th>\n",
       "      <td>2.71%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>2.87%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>6.74%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>1.85%</td>\n",
       "      <td>14.17%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sales Associate III</th>\n",
       "      <td>0.00%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>2.48%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>4.50%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>6.98%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sales Associate IV</th>\n",
       "      <td>0.00%</td>\n",
       "      <td>1.21%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>4.31%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>5.52%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sales Associate V</th>\n",
       "      <td>0.00%</td>\n",
       "      <td>3.57%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>3.57%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sales Representative</th>\n",
       "      <td>2.05%</td>\n",
       "      <td>3.51%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>2.12%</td>\n",
       "      <td>1.09%</td>\n",
       "      <td>3.43%</td>\n",
       "      <td>0.82%</td>\n",
       "      <td>5.22%</td>\n",
       "      <td>2.09%</td>\n",
       "      <td>20.33%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Senior Sales Associate</th>\n",
       "      <td>0.00%</td>\n",
       "      <td>2.88%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>5.54%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>8.42%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Senior Sales Representative</th>\n",
       "      <td>8.83%</td>\n",
       "      <td>2.48%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>1.97%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>2.67%</td>\n",
       "      <td>15.95%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Totals</th>\n",
       "      <td>13.59%</td>\n",
       "      <td>16.22%</td>\n",
       "      <td>1.36%</td>\n",
       "      <td>11.77%</td>\n",
       "      <td>4.48%</td>\n",
       "      <td>18.34%</td>\n",
       "      <td>6.45%</td>\n",
       "      <td>13.53%</td>\n",
       "      <td>14.25%</td>\n",
       "      <td>100.00%</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "sales_region                 Central East  N Central East  N Central West  \\\n",
       "job_title                                                                   \n",
       "Sales Associate                     0.00%           2.57%           0.00%   \n",
       "Sales Associate I                   0.00%           0.00%           1.36%   \n",
       "Sales Associate II                  2.71%           0.00%           0.00%   \n",
       "Sales Associate III                 0.00%           0.00%           0.00%   \n",
       "Sales Associate IV                  0.00%           1.21%           0.00%   \n",
       "Sales Associate V                   0.00%           3.57%           0.00%   \n",
       "Sales Representative                2.05%           3.51%           0.00%   \n",
       "Senior Sales Associate              0.00%           2.88%           0.00%   \n",
       "Senior Sales Representative         8.83%           2.48%           0.00%   \n",
       "Totals                             13.59%          16.22%           1.36%   \n",
       "\n",
       "sales_region                 Northeast  Northwest  S Central East  \\\n",
       "job_title                                                           \n",
       "Sales Associate                  0.00%      0.00%           0.00%   \n",
       "Sales Associate I                0.00%      1.42%           2.63%   \n",
       "Sales Associate II               2.87%      0.00%           6.74%   \n",
       "Sales Associate III              2.48%      0.00%           0.00%   \n",
       "Sales Associate IV               4.31%      0.00%           0.00%   \n",
       "Sales Associate V                0.00%      0.00%           0.00%   \n",
       "Sales Representative             2.12%      1.09%           3.43%   \n",
       "Senior Sales Associate           0.00%      0.00%           5.54%   \n",
       "Senior Sales Representative      0.00%      1.97%           0.00%   \n",
       "Totals                          11.77%      4.48%          18.34%   \n",
       "\n",
       "sales_region                 S Central West  Southeast  Southwest  Totals  \n",
       "job_title                                                                  \n",
       "Sales Associate                       1.70%      0.00%      2.69%   6.96%  \n",
       "Sales Associate I                     3.94%      3.80%      4.95%  18.11%  \n",
       "Sales Associate II                    0.00%      0.00%      1.85%  14.17%  \n",
       "Sales Associate III                   0.00%      4.50%      0.00%   6.98%  \n",
       "Sales Associate IV                    0.00%      0.00%      0.00%   5.52%  \n",
       "Sales Associate V                     0.00%      0.00%      0.00%   3.57%  \n",
       "Sales Representative                  0.82%      5.22%      2.09%  20.33%  \n",
       "Senior Sales Associate                0.00%      0.00%      0.00%   8.42%  \n",
       "Senior Sales Representative           0.00%      0.00%      2.67%  15.95%  \n",
       "Totals                                6.45%     13.53%     14.25% 100.00%  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.set_option(\"display.float_format\", \"{:.2%}\".format)\n",
    "\n",
    "pd.crosstab(\n",
    "    index=sales_data.job_title,\n",
    "    columns=sales_data.sales_region,\n",
    "    margins=True,\n",
    "    margins_name=\"Totals\",\n",
    "    normalize=True,\n",
    ")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
